const router = require('koa-router')()
const connection = require('./db'); // 导入数据库连接对象
const query = 'SELECT * FROM course';
router.get("/getcourselist", async (ctx, next) => {
    let request=ctx.request;
    let reqquery = request.query;
    console.log(reqquery.page);
    try {
      // 使用 Promise 封装数据库查询
      const queryAsync = () => {
        return new Promise((resolve, reject) => {
          connection.query(query, function (error, results, fields) {
            if (error) {
              reject(error);
            } else {
              resolve(results);
            }
          });
        });
      };
  
      // 使用 await 等待查询结果
      const list = await queryAsync();
      //console.log(JSON.stringify(list));
      const parsedList = JSON.parse(JSON.stringify(list)); 
      console.log(parsedList);
      const page = reqquery.page ;
      const perpage = reqquery.perpage ;
      console.log('页码:', page, '每页数量:', perpage);
      ctx.body = {
            list: parsedList,
            length: parsedList.length
        };
    } catch (error) {
      // 处理错误
      console.error(error);
      ctx.status = 500;
      ctx.body = "Internal Server Error";
    }
  });
  
  router.delete("/deletecourse/:id", async (ctx, next) => {    
    let courseId = ctx.params.id;

    try {
        const deleteQuery = `DELETE FROM course WHERE id = ?`;

        // 使用 Promise 封装数据库删除操作
        const deleteAsync = () => {
            return new Promise((resolve, reject) => {
                connection.query(deleteQuery, [courseId], function (error, results, fields) {
                    if (error) {
                        reject(error);
                    } else {
                        resolve(results);
                    }
                });
            });
        };

        // 使用 await 等待删除操作的结果
        const deleteResult = await deleteAsync();

        if (deleteResult.affectedRows > 0) {
            console.log(`course with ID ${courseId} deleted successfully`);
            ctx.body = 'course deleted successfully';
        } else {
            console.log(`course with ID ${courseId} not found`);
            ctx.status = 404; // 资源未找到
            ctx.body = 'course not found';
        }
    } catch (error) {
        // 处理错误
        console.error(`Error deleting course with ID ${courseId}: ${error.message}`);
        ctx.status = 500; // 内部服务器错误
        ctx.body = 'Error deleting course';
    }
});


router.post('/changecourse', async (ctx, next) => {
  // 处理 GET 请求
  console.log('收到针对 /changecourse 的 GET 请求');
  // 使用 ctx.query 访问查询参数
  const Id = ctx.request.body.id;
  const values = ctx.request.body.values;
  const requestData = ctx.request.body;
  console.log('收到的前端数据：', Id,values);
  try {
    // 执行数据库更新操作
    const updateQuery = 'UPDATE course SET ? WHERE id = ?';
    await connection.query(updateQuery, [values, Id]);

    // 检查是否成功更新数据
    console.log('成功更新课程数据');
    ctx.body = '成功更新课程数据';
  } catch (error) {
    console.error('更新课程数据时出现错误：', error);
    ctx.status = 500;
    ctx.body = '更新课程数据时出现错误';
  }
});

router.get("/searchcourse", async (ctx, next) => {
  const { keyword } = ctx.query; // 从请求参数中获取关键词
  console.log('从请求参数中获取关键词'+keyword);
  try {
      // 构建 SQL 查询语句
      const searchQuery = `SELECT * FROM course WHERE coursename LIKE '%${keyword}%' OR courseid LIKE '%${keyword}%'`;
      
      // 使用 Promise 封装数据库查询
      const queryAsync = () => {
        return new Promise((resolve, reject) => {
          connection.query(searchQuery, function (error, results, fields) {
            if (error) {
              reject(error);
            } else {
              resolve(results);
            }
          });
        });
      };

      // 使用 await 等待查询结果
      const searchResults = await queryAsync();

      console.log('匹配到的数据', searchResults);

      // 返回搜索结果
      ctx.body = {
          results: searchResults,
          length: searchResults.length  // 返回结果的长度
      };
  } catch (error) {
    // 处理错误
    console.error(error);
    ctx.status = 500;
    ctx.body = "Internal Server Error";
  }
});

router.post('/addcourse', async (ctx, next) => {
  // 处理 GET 请求
  console.log('收到针对 /addcourse 的 GET 请求');
  // 使用 ctx.query 访问查询参数
  const values = ctx.request.body.values;
  const requestData = ctx.request.body;
  console.log('收到的前端数据：',values);
  try {
    // 执行数据库更新操作
    const addQuery = 'INSERT INTO course SET ?';
    await connection.query(addQuery, values);
 

    // 检查是否成功更新数据
    console.log('成功添加课程数据');
    ctx.body = '成功添加课程数据';
  } catch (error) {
    console.error('添加啊课程数据时出现错误：', error);
    ctx.status = 500;
    ctx.body = '添加课程数据时出现错误';
  }
});

router.post("/gettaketeacher", async (ctx, next) => {
  const courseId = ctx.request.body.courseid; // 从请求体中获取courseid
  
  try {
    // 构建SQL查询语句
    const takequery = `SELECT * FROM \`teacher-course\` WHERE courseid = '${courseId}'`;

    // 执行SQL查询
    const results = await new Promise((resolve, reject) => {
      connection.query(takequery, async (error, results, fields) => {
        if (error) {
          reject(error); // 如果发生错误，则reject Promise
        } else {
          // 取得teacherid列表
          const teacherIds = results.map(result => result.teacherid);
          if (teacherIds.length === 0) {
            // 如果课程ID列表为空，返回空数组
            resolve([]);
          } else {
            // 构建查询课程信息的SQL语句
            const teacherquery = `SELECT * FROM teacher WHERE teacherid IN (${teacherIds.join(',')})`;
            
            // 查询课程信息
            connection.query(teacherquery, (error, teacherResults, fields) => {
              if (error) {
                reject(error); // 如果发生错误，则reject Promise
              } else {
                resolve(teacherResults); // 成功时解析结果
              }
            });
          }
        }
      });
    });

    // 响应查询结果
    ctx.body = results;
  } catch (error) {
    // 处理错误情况
    ctx.status = 500;
    ctx.body = { error: "An error occurred while processing the request" };
  }
});

  module.exports = router;
  